package com.emuporium.gymrat.server.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

public class RoutineDB {
	public static int copyRoutine(Routine routine, User newOwner) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		Statement s = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = 0;

		String query = "insert into routines (name, description, author, creator_id,"
				+ " primarygroup, secondarygroup,sourceid,ownerid)\r\n"
				+ "values (?,?,?,?,?,?,?,?)";
		String query2 = "select last_insert_id()";
		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, routine.getName());
			ps.setString(2, routine.getDescription());
			ps.setString(3, routine.getAuthor());
			ps.setInt(4, routine.getCreatorId());
			ps.setInt(5, routine.getPrimary());
			ps.setInt(6, routine.getSecondary());
			ps.setInt(7, routine.getId());
			ps.setInt(8, newOwner.getId());
			result = ps.executeUpdate();
			if (result > 0) {
				s = connection.createStatement();
				rs = s.executeQuery(query2);
				if (rs.first()) {
					int id = rs.getInt(1);

					for (Entry e : routine.getEntries()) {
						ArrayList<String> variations = new ArrayList<String>();
						for (int i : e.getVariations().keySet()) {
							variations.add(new String("" + i)); // FIXME: this
							// whole thing
							// sucks
						}
						insertEntry(id, e.getExerciseId(), e.getOrder(), e
								.getSets(), e.getReps(), variations, e
								.getNote());

					}
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return result;
	}

	public static int delete(Routine routine) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "DELETE FROM routines WHERE _id = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, routine.getId());

			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
	}

	public static int deleteEntries(ArrayList<String> entries) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "delete from routineentries where _id = ?";
		int result = 0;

		try {
			for (String variation : entries) {
				ps = connection.prepareStatement(query);
				ps.setString(1, variation);
				result = ps.executeUpdate();
			}

			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return result;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

	}

	public static ArrayList<Routine> getRoutines(String email) {
		ArrayList<Routine> list = new ArrayList<Routine>();

		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "select routines._id as id,name, description, author, account.email,\r\n"
				+ "primarygroup, secondarygroup, shared,sourceid, vote, (select count(distinct ownerid) from routines where id = sourceid) as users \r\n"
				+ "from routines\r\n"
				+ "join account on (account._id = routines.ownerid)\r\n"
				+ "left join routinevotes on (sourceid = routinevotes.routineid) "
				+ "where account.email = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, email);

			rs = ps.executeQuery();

			while (rs.next()) {
				Routine r = new Routine();
				r.setName(rs.getString("name"));
				r.setDescription(rs.getString("description"));
				r.setAuthor(rs.getString("author"));
				r.setId(rs.getInt("id"));
				r.setPrimary(rs.getInt("primarygroup"));
				r.setSecondary(rs.getInt("secondarygroup"));
				r.setShared(rs.getBoolean("shared"));
				r.setSourceId(rs.getInt("sourceid"));
				r.setUsers(rs.getInt("users"));
				r.setMyVote(rs.getInt("vote"));
				list.add(r);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return list;
	}

	public static int insert(Routine routine, int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		Statement s = null;
		ResultSet rs = null;

		String query = "insert into routines (name,description,author,creator_id,ownerid)"
				+ " values (?,?,?,?,?)";
		String query2 = "select last_insert_id()";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, routine.getName());
			ps.setString(2, routine.getDescription());
			ps.setString(3, routine.getAuthor());
			ps.setInt(4, userId);
			ps.setInt(5, userId);
			if (ps.executeUpdate() > 0) {
				s = connection.createStatement();
				rs = s.executeQuery(query2);
				rs.first();
				return (rs.getInt("last_insert_id()"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return 0;
	}

	public static int insertEntry(int routineId, int exerciseId, int position,
			int sets, int reps, ArrayList<String> variationId, String notes) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		Statement s = null;
		ResultSet rs = null;

		String query = "insert into routineentries (routineid,exerciseid,position,sets,reps,notes)\r\n"
				+ "values(?,?,?,?,?,?);";
		String varQuery = "insert into entryvariations(entryid,variationid) values (?,?)";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, routineId);
			ps.setInt(2, exerciseId);
			ps.setInt(3, position);
			ps.setInt(4, sets);
			ps.setInt(5, reps);
			ps.setString(6, notes);

			int partial = ps.executeUpdate();

			s = connection.createStatement();
			rs = s.executeQuery("select last_insert_id() as id");
			rs.first();
			int id = rs.getInt("id");

			for (String var : variationId) {
				ps = connection.prepareStatement(varQuery);
				ps.setInt(1, id);
				ps.setString(2, var);
				ps.executeUpdate();
			}

			return partial;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return 0;

	}

	public static ArrayList<Routine> searchRoutines(ArrayList<String> keywords,
			int userId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		ArrayList<Routine> result = new ArrayList<Routine>();
		String query = "select *, _id as id, (select count(distinct ownerid) from routines where id = sourceid) as users," +
				"(select count(distinct accountid) from routinevotes where vote=1 and id=routineid) as votesup,\r\n" + 
				"(select count(distinct accountid) from routinevotes where vote=-1 and id=routineid) as votesdown \r\n"
				+ "from routines\r\n"
				+ "where shared = 1\r\n"
				+ "and creator_id != ?";

		try {

			ps = connection.prepareStatement(query);
			ps.setInt(1, userId);
			rs = ps.executeQuery();

			while (rs.next()) {
				Routine routine = new Routine();
				routine.setName(rs.getString("name"));
				routine.setAuthor(rs.getString("author"));
				routine.setDescription(rs.getString("description"));
				routine.setId(rs.getInt("_id"));
				routine.setPrimary(rs.getInt("primarygroup"));
				routine.setSecondary(rs.getInt("secondarygroup"));
				routine.setShared(rs.getBoolean("shared"));
				routine.setCreatorId(rs.getInt("creator_id"));
				routine.setUsers(rs.getInt("users"));
				routine.setVotesUp(rs.getInt("votesup"));
				routine.setVotesDown(rs.getInt("votesdown"));

				result.add(routine);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return result;
	}

	public static Entry selectEntry(int entryId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		Entry result = new Entry();
		String query = "select routineentries._id, exercises._id as \'exerciseid\', \r\n"
				+ "exercises.name as \'exercisename\', position, sets, reps, notes, musclegroups.musclegroup,\r\n"
				+ "variations._id AS variationid, variation\r\n"
				+ "from routineentries\r\n"
				+ "join routines on (routineid = routines._id)\r\n"
				+ "join exercises on (exerciseid = exercises._id)\r\n"
				+ "join musclegroups on (exercises.musclegroup = musclegroups._id)\r\n"
				+ "left join entryvariations on (routineentries._id = entryvariations.entryid)\r\n"
				+ "left join variations on (variations._id = entryvariations.variationid)\r\n"
				+ "where routineentries._id = ?";

		try {

			ps = connection.prepareStatement(query);
			ps.setInt(1, entryId);
			rs = ps.executeQuery();

			if (rs.first()) {
				result.setId(rs.getInt("_id"));
				result.setExercise(rs.getString("exercisename"));
				result.setExerciseId(rs.getInt("exerciseid"));
				result.setMuscleGroup(rs.getString("musclegroup"));
				result.setNote(rs.getString("notes"));
				result.setSets(rs.getInt("sets"));
				result.setReps(rs.getInt("reps"));
				result.setOrder(rs.getInt("position"));
				if (rs.getString("variation") != null)
					result.addVariation(rs.getInt("variationid"), rs
							.getString("variation"));

				while (rs.next()) {
					result.addVariation(rs.getInt("variationid"), rs
							.getString("variation"));
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return result;
	}

	public static Routine selectRoutine(int id) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		PreparedStatement innerPs = null;
		ResultSet rs = null;
		ResultSet innerRs = null;
		Routine routine = new Routine();

		String query = "select _id as id, name,description,author, primarygroup, "
				+ "secondarygroup, ownerid, shared, creator_id,sourceid, (select count(distinct ownerid) from routines where id = sourceid) as users from routines\r\n"
				+ "where routines._id = ?";
		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.first()) {
				routine.setName(rs.getString("name"));
				routine.setAuthor(rs.getString("author"));
				routine.setDescription(rs.getString("description"));
				routine.setId(id);
				routine.setPrimary(rs.getInt("primarygroup"));
				routine.setSecondary(rs.getInt("secondarygroup"));
				routine.setShared(rs.getBoolean("shared"));
				routine.setCreatorId(rs.getInt("creator_id"));
				routine.setSourceId(rs.getInt("sourceid"));
				routine.setUsers(rs.getInt("users"));
				routine.setLogs(LogDB.selectLogEntries(rs.getInt("ownerid"), null, null, id, -1));

				// fetch all of the entries for this routine
				String query2 = "select routineentries._id, routineentries.sets, notes, routineentries.reps,\r\n"
						+ "routineentries.position, exercises._id AS exid, exercises.name, musclegroups.musclegroup\r\n"
						+ "from routineentries \r\n"
						+ "join exercises on (routineentries.exerciseid = exercises._id)\r\n"
						+ "join musclegroups on (exercises.musclegroup = musclegroups._id)\r\n"
						+ "where routineentries.routineid = ? order by position";

				// fetch the variations for each entry
				String query3 = "select variations._id, variation from variations\r\n"
						+ "join entryvariations on (entryvariations.variationid = variations._id)\r\n"
						+ "where entryid = ?";

				ps = connection.prepareStatement(query2);
				ps.setInt(1, id);
				rs = ps.executeQuery();

				while (rs.next()) {
					Entry entry = new Entry();
					entry.setExercise(rs.getString("name"));
					entry.setExerciseId(rs.getInt("exid"));
					entry.setMuscleGroup(rs.getString("musclegroup"));
					entry.setSets(rs.getInt("sets"));
					entry.setReps(rs.getInt("reps"));
					entry.setOrder(rs.getInt("position"));
					entry.setId(rs.getInt("_id"));
					entry.setNote(rs.getString("notes"));

					innerPs = connection.prepareStatement(query3);
					innerPs.setInt(1, entry.getId());
					innerRs = innerPs.executeQuery();
					while (innerRs.next()) {
						entry.addVariation(innerRs.getInt("_id"), innerRs
								.getString("variation"));
					}

					routine.addEntry(entry);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return routine;
	}

	public static int update(Routine routine) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "UPDATE routines SET name = ?, description = ?,"
				+ "author = ?, primarygroup=?, secondarygroup=?, shared=? "
				+ "where _id = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setString(1, routine.getName());
			ps.setString(2, routine.getDescription());
			ps.setString(3, routine.getAuthor());
			ps.setInt(4, routine.getPrimary());
			ps.setInt(5, routine.getSecondary());
			ps.setBoolean(6, routine.isShared());
			ps.setInt(7, routine.getId());

			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

	}

	public static int updateEntry(int id, int order, int sets, int reps,
			String notes, ArrayList<Integer> variationids) {

		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		String query = "update routineentries\r\n"
				+ "set position=?, sets=?, reps=?, notes=?\r\n"
				+ "where _id = ?";
		String query2 = "delete from entryvariations where entryid=?";
		String query3 = "insert into entryvariations (entryid, variationid)\r\n"
				+ "values (?,?)";
		int result;

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, order);
			ps.setInt(2, sets);
			ps.setInt(3, reps);
			ps.setString(4, notes);
			ps.setInt(5, id);
			result = ps.executeUpdate();

			ps = connection.prepareStatement(query2);
			ps.setInt(1, id);
			ps.executeUpdate();

			for (Integer i : variationids) {
				ps = connection.prepareStatement(query3);
				ps.setInt(1, id);
				ps.setInt(2, i);
				ps.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return result;
	}

	public static void vote(int routineId, int userId, int vote){
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String query = "insert into routinevotes (accountid, routineid, vote)" +
				" values(?,?,?)";
		String query2 = "update routinevotes set vote = ? " +
				"where accountid = ? and routineid = ?";
		
		if(vote > 1)
			vote = 1;
		if(vote < -1)
			vote = -1;
		
		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, userId);
			ps.setInt(2, routineId);
			ps.setInt(3, vote);
			ps.executeUpdate();

			//insert failed, update instead
		} catch (MySQLIntegrityConstraintViolationException e){
			try {
			ps = connection.prepareStatement(query2);
			ps.setInt(1, vote);
			ps.setInt(2, userId);
			ps.setInt(3, routineId);
			ps.executeUpdate();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
	}
}
